<?php

namespace Repository;

use Doctrine\ORM\EntityRepository;

class ProductRepository extends EntityRepository {
	
	public function backendPaginate($conf){
		$q = $this->_em->getRepository('\Entity\ShProduct')->findBy(
			$conf['condition'], $conf['order'], $conf['limit'], $conf['offset']
		);
		
		return $q;
	}
	
	public function total_rows($pag){
		$q = $this->_em->createQueryBuilder()
		                ->select('COUNT(u.id)')
		                ->from('Entity\ShProduct','u');
		return $q->getQuery()->getSingleScalarResult();
	}
	
	public function total_rows_cat($cat){
		$q = $this->_em->createQueryBuilder()
		                ->select('COUNT(u.id)')
		                ->from('Entity\ShProduct','u')
						->where('u.category = ?1')
						->setParameters(array(
							'1' => $cat
						));
						
		return $q->getQuery()->getSingleScalarResult();
	}
	
	public function remove_product($product){
		$this->_em->remove($product);
		$this->_em->flush();
		return true;
	}
	
	public function edit_product(\Entity\ShProduct $product, $data){

		$cat = $this->_em->getRepository('\Entity\ShCategory')->findOneById($data->post('chung-loai'));
		
		if(empty($cat))
			return false;
		
		$product->setName($data->post('ten'));
		$product->setCategory($cat);
		$product->setPrice($data->post('gia'));
		
		$img = $this->_em->getRepository('\Entity\ShImage')->findOneBy(array(
			'id' => $data->post('thumb')
		));
		
		$product->setImage($img);
		$product->setQuantity($data->post('so-luong'));
		$product->setContent($data->post('chi-tiet'));
		$product->setDate(time());

		$this->_em->persist($product);
		$this->_em->flush();
		
		$this->resetProductExtension($product);
		$this->addColor($data->post('color'), $product, true);
		$this->addSize($data->post('size'), $product, true);
		return true;
	}
	
	public function resetProductExtension($product){
		$q = $this->_em->createQueryBuilder();
		$q->delete('\Entity\ShProductdetail', 'e');
		$q->andWhere('e.product = :p_id');
		$q->setParameter(':p_id', $product->getId());
		$q->getQuery()->execute();
	}
	
	public function findMostWanted(){
		$q = $this->_em->createQueryBuilder()
				->select('SUM(cd.quantity) as total, p.name as p_name, p.id as p_id, p.price as p_price, img.name as p_image')
				->from('Entity\ShCartdetail', 'cd')
				->leftJoin('cd.shCart','c')
				->leftJoin('cd.shProduct', 'p')
				->leftJoin('p.image', 'img')
				->where('c.status >= 0')
				->andWhere('p.status = 1')
				->andWhere('p.quantity >= 0')
				->groupBy('cd.shProduct')
				->setMaxResults(9)
				->orderBy('total','desc');

		return $q->getQuery()->getResult();		
	}
	
	public function addColor($color, $product){
		
		$color = $this->_em->getRepository('\Entity\ShExtension')->findBy(array(
			'id' => $color,
			'status' => 1,
			'type' => 2
		));
		if(!empty($color)):
			foreach($color as $v):
				$new_color = new \Entity\ShProductdetail();
				$new_color->setProduct($product);
				$new_color->setExtension($v);
				$this->_em->persist($new_color);
			endforeach;
			$this->_em->flush();
		endif;
		
		return true;
	}
	
	public function addSize($size, $product){

		$size = $this->_em->getRepository('\Entity\ShExtension')->findBy(array(
			'id' => $size,
			'status' => 1,
			'type' => 3
		));
		if(!empty($size)):
			foreach($size as $v):
				$new_size = new \Entity\ShProductdetail();
				$new_size->setProduct($product);
				$new_size->setExtension($v);
				$this->_em->persist($new_size);
			endforeach;
			$this->_em->flush();
		endif;
		
		return true;
	}
	
	public function findExtension($id){
		$query = $this->_em->createQueryBuilder()
					->select('e.name, e.id, e.val, e.type')
					->from('\Entity\ShProductdetail','pd')
					->join('pd.extension','e')
					->where('pd.product = ?1')
					->andWhere('e.status = 1')
					->andWhere('e.type IN(2,3)')
					->setParameters(array(1 => $id))
					->getQuery()->getResult();
		
		$data = array('size'=>array(), 'color' => array());
		foreach($query as $v):
			if($v['type'] == 2)
				$data['color'][] = $v;
			elseif($v['type'] == 3)
				$data['size'][] = $v;
		endforeach;
		return $data;
	}
	
	public function findSimilar($id, $catid){
		$query = $this->_em->createQueryBuilder()
							->select('p')
							->from('\Entity\ShProduct','p')
							->where('p.category = ?1')
							->andWhere('p.id != ?2')
							->setMaxResults(8)
							->setParameters(array(1 => $catid, 2 => $id))
							->getQuery()->getResult();
		return $query;
	}
	
	public function findProductForCart($id, $qty){
		$query = $this->_em->createQueryBuilder()
							->select('p')
							->from('\Entity\ShProduct','p')
							->leftJoin('p.image', 'i')
							->where('p.id = ?1')
							->andWhere('p.status = 1')
							->andWhere('p.quantity >= ?2 OR p.quantity = -1')
							->setMaxResults(1)
							->setParameters(array(1 => $id, 2 => $qty))
							->getQuery()->getOneOrNullResult();
		return $query;
	}
	
	public function admin_search_for_cart($val){
		$query = $this->_em->createQueryBuilder();
		$query->select('p.id, p.name, img.name as image, p.price')
			->from('\Entity\ShProduct', 'p')
			->leftJoin('p.image', 'img')
			->where(
				$query->expr()->like('p.name', '?1')
			)
			->orderBy('p.name', 'ASC')
			->setParameters(array(
				1 => '%'.$val.'%',
			));

		return $query->getQuery()->getResult();
	}


	public function front_search_product($q, $pag){
		$query = $this->_em->createQueryBuilder();
		$query->select('p')
			->from('\Entity\ShProduct', 'p')
			->leftJoin('p.image', 'img')
			->where(
				$query->expr()->like('p.name', '?1')
			)
			->orderBy('p.'.$pag['sortBy'], $pag['sortOrder'])
			->setFirstResult($pag['offset'])
			->setMaxResults($pag['limit'])
			->setParameters(array(
				1 => '%'.$q.'%',
			));

		$pag = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
		return array(
			'count' => count($pag),
			'result' => $pag->getQuery()->getResult()
		);
	}

	public function getExtension($product){
		$ids = $val = array();
		foreach($product as $v):
			$ids[] = $v['id'];
			$val[$v['id']] = $v;
		endforeach;
			
		$query = $this->_em->createQueryBuilder();
		$query->select('IDENTITY(pd.product) as p_id, e.id as e_id, e.name, e.val, e.type')
			->from('\Entity\ShProductdetail', 'pd')
			->leftJoin('pd.extension', 'e')
			->where('pd.product in(?1)')
			->setParameters(array(
				1 => $ids
			));
		$data =  $query->getQuery()->getResult();
		
		foreach($data as $v):
			if($v['type'] ==2)
				$val[$v['p_id']]['color'][] = array(
					'id' => $v['e_id'],
					'name' => $v['name'],
					'val' => $v['val']
				);
			elseif($v['type'] == 3)
				$val[$v['p_id']]['color'][] = array(
					'id' => $v['e_id'],
					'name' => $v['name'],
					'val' => $v['val']
				);
		endforeach;
		
		return $val;
	}
	
	public function findProductColor($id){
		$query = $this->_em->createQueryBuilder();
		$query->select('e.name, e.val')
			->from('\Entity\ShProductdetail', 'pd')
			->leftJoin('pd.extension', 'e')
			->where('pd.product = ?1')
			->andWhere('e.status = 1')
			->andWhere('e.type = 2')
			->setParameters(array(
				1 => $id
			));
			
		return $query->getQuery()->getResult();
	}
	
	public function findProductExtension($id, $type = 2){
	
		if($type == 2)
			$type = 2;
		else
			$type = 3;
		
		$query = $this->_em->createQueryBuilder();
		$query->select('e.id')
			->from('\Entity\ShProductdetail', 'pd')
			->leftJoin('pd.extension', 'e')
			->where('pd.product = ?1')
			->andWhere('e.status = 1')
			->andWhere('e.type = '.$type)
			->setParameters(array(
				1 => $id
			));
			
		$data = $query->getQuery()->getResult();
		$val = array();
		
		foreach($data as $v)
			$val[] = $v['id'];
		return $val;
	}
	
	public function findWithExtension($id, $data){
		$query = $this->_em->createQueryBuilder()
							->select('e.name, e.val, e.id')
							->from('\Entity\ShProductdetail','pd')
							->join('pd.extension', 'e')
							->where('pd.product = ?1')
							->andWhere('e.id IN(?2)')
							->andWhere('e.type IN(2,3)')
							->andWhere('e.status = 1')
							->setParameters(array(
								1 => $id,
								2 => array($data['options']['size'], $data['options']['color'])
							));
		return $query->getQuery()->getResult();
	}
}